import mysql.connector


my_db = mysql.connector.connect(
    host="localhost",
    user="root",
    password="root8.0.28",
    # 创建表
    database="python_db"
)

# print(my_db)


my_cur = my_db.cursor()

# 1.创建数据库(CREATE 语句)
# my_cur.execute("CREATE DATABASE python_db")

# 2.创建表(前提需要数据库存在)
# my_cur.execute("CREATE TABLE sites (name VARCHAR(255),url VARCHAR(255))")

# 3.加主键
# my_cur.execute("ALTER TABLE sites ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY")
# 3.查询存在的表
# my_cur.execute("SHOW TABLES")

'''
for x in my_cur:
    print(x)
'''

# 4.插入数据
# sql = "INSERT INTO sites (name, url) VALUES (%s, %s)"

'''
# 单条插入
val = ("RUNOOB", "https://www.runoob.com")
my_cur.execute(sql, val)
'''


'''
# 批量插入
val2 = [
    ('Google', 'https://www.google.com'),
    ('Github', 'https://www.github.com'),
    ('Taobao', 'https://www.taobao.com'),
    ('stackoverflow', 'https://www.stackoverflow.com/')
]

my_cur.executemany(sql, val2)
'''

# 数据表内容有更新，必须使用到该语句
# my_db.commit()
# print(my_cur.rowcount, "记录插入成功!")

# 查询
sql = "SELECT * FROM sites"
my_cur.execute(sql)

# 获取一条数据
# my_cur.fetchone()

results = my_cur.fetchall()    # 获取所有记录

for x in results:
    print(x)
